SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TTSEMA_FLUJ_REAL_Q03]              
/*-------------------------------------------------------*/              
/*---Empresa               OFISIS S.A.                --*/              
/*---Cliente               CELIMA                     --*/              
/*---Sistema               Tesoreria                  --*/              
/*---M¢dulo                Empresa                    --*/              
/*---Programa              DESCRIPCION DE SEMANAS SEGUN CLASIFICACION (REAL)--*/              
/*---Script                SP_TCMOVI_BANC_Q03.sql     --*/              
/*---Nombre SP             SP_TCMOVI_BANC_Q03         --*/              
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/              
/*---Fecha Creaci¢n        25/09/2001                 --*/              
/*---Base Datos            SQL SERVER        --*/              
/*---Versi¢n               2000                       --*/              
/*---Invoca a SP                                      --*/              
/*-------------------------------------------------------*/              
              
/*-------------------------------------------------------*/              
/*---Modificado 1 por           --*/              
/*---Fecha Modificaci¢n         --*/               
/*---Detalle Modificaci¢n       --*/              
/*-------------------------------------------------------*/              
              
/*-------------------------------------------------------*/              
/*---Drop Proc SP_TCMOVI_BANC_Q03                      --*/              
/*---Grant all on SP_TCMOVI_BANC_Q03 to public         --*/               
/*---SP_TCMOVI_BANC_Q03 '01', 'DOL', 10, 2001, 11, 2001  --*/              
/*-------------------------------------------------------*/              
              
--EXEC UL_TTSEMA_FLUJ_Q01 '01', 2007, 1, 5  
@ISCO_EMPR Varchar(2) ,                    
@IDFE_OPER_INIC TD_DT_001,              
@IDFE_OPER_FINA TD_DT_001   
/*,              
@INNU_MESE_FINA TD_IN_001,              
@INNU_ANIO_FINA TD_IN_001  */            
As              
CREATE TABLE #TWSEMA_FLUJ_Q01              
 ( CO_EMPR      varchar(3) NULL,              
   FE_SE01 VARCHAR(20)NULL,  
   FE_SE02 VARCHAR(20)NULL,  
   FE_SE03 VARCHAR(20)NULL,  
   FE_SE04 VARCHAR(20)NULL,  
   FE_SE05 VARCHAR(20)NULL,  
   FE_SE06 VARCHAR(20)NULL,  
   FE_SE07 VARCHAR(20)NULL,  
   FE_SE08 VARCHAR(20)NULL,  
   FE_SE09 VARCHAR(20)NULL,  
   FE_SE10 VARCHAR(20)NULL,  
   FE_SE11 VARCHAR(20)NULL,  
   FE_SE12 VARCHAR(20)NULL)              
          
              
DECLARE              
              
@VNNU_MESE  TD_IN_001,              
@VNNU_ANIO  TD_IN_001,              
@VSNU_MESE  TD_VC_002,              
@VSNU_ANIO  VARCHAR(4),              
@VNNU_CONT_SEMA TD_IN_001,              
@VSDE_SELE VARCHAR(6000),              
@VSCO_EMPR VARCHAR (50),              
@VSDE_CAMP VARCHAR(7) ,          
@VSCO_MONE_NACI TD_VC_003,          
@VSCO_MONE_EXTR TD_VC_003,        
@VNNU_FACT  NUMERIC(16,4),  
@VNNU_SEMA_ACTU TD_IN_001,   
@VSFE_OPER_INIC TD_VC_020,    
@VSFE_OPER_FINA TD_VC_020,    
@VSFE_INIC_SEMA TD_VC_020,    
@VSFE_FINA_SEMA TD_VC_020,  
@VNNU_SEMA_INIC TD_IN_001,  
@VNNU_SEMA_FINA TD_IN_001,  
@VSSIGN VARCHAR(20)    
  
  
SELECT @VSFE_OPER_INIC = CONVERT(VARCHAR(10), @IDFE_OPER_INIC, 103)       
SELECT @VSFE_OPER_FINA = CONVERT(VARCHAR(10), @IDFE_OPER_FINA, 103)       
SELECT @VNNU_ANIO = YEAR(@IDFE_OPER_INIC)   
  
SELECT @VNNU_SEMA_INIC = NU_SEMA      
FROM TTSEMA_FLUJ      
WHERE CO_EMPR = @ISCO_EMPR      
AND NU_ANIO = YEAR(@IDFE_OPER_INIC)      
AND FE_INIC_SEMA < = Convert(Datetime, @VSFE_OPER_INIC, 103)        
AND FE_FINA_SEMA > = Convert(Datetime, @VSFE_OPER_INIC, 103)       
      
      
      
--SELECT *FROM TTSEMA_FLUJ      
      
SELECT @VNNU_SEMA_FINA = NU_SEMA      
FROM TTSEMA_FLUJ      
WHERE CO_EMPR = @ISCO_EMPR      
AND NU_ANIO = YEAR(@IDFE_OPER_FINA)      
AND FE_INIC_SEMA < = Convert(Datetime, @VSFE_OPER_FINA, 103)        
AND FE_FINA_SEMA > = Convert(Datetime, @VSFE_OPER_FINA, 103)       
  
IF (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC + 1) >  12 
BEGIN   
    SELECT @VNNU_SEMA_ACTU = @VNNU_SEMA_FINA - 11  
END   
ELSE  
BEGIN  
    SELECT @VNNU_SEMA_ACTU = @VNNU_SEMA_INIC  
END   
             
  INSERT INTO #TWSEMA_FLUJ_Q01          
  (CO_EMPR,  FE_SE01)              
   SELECT CO_EMPR,  REPLICATE( '0', 2-LEN(RTRIM(LTRIM(STR(NU_SEMA))))) + LTRIM(RTRIM(STR(NU_SEMA))) + REPLICATE( '0', 2-LEN(RTRIM(LTRIM(DAY(FE_INIC_SEMA))))) + LTRIM(RTRIM(DAY(FE_INIC_SEMA))) + '-' + SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3) +' ' +   




REPLICATE( '0', 2-LEN(RTRIM(LTRIM(DAY(FE_FINA_SEMA))))) + LTRIM(RTRIM(DAY(FE_FINA_SEMA))) + '-' + SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3)                  
   FROM TTSEMA_FLUJ  
   WHERE CO_EMPR = @ISCO_EMPR  
   AND NU_ANIO = @VNNU_ANIO  
   AND NU_SEMA = @VNNU_SEMA_ACTU  
        
  
SELECT @VNNU_CONT_SEMA = 2  
SELECT @VNNU_SEMA_ACTU = @VNNU_SEMA_ACTU + 1  
SELECT @VSSIGN = " + '-' + "  
  
  
    
SELECT @VSDE_CAMP = 'FE_SE' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_SEMA))))) + RTRIM(LTRIM(STR(@VNNU_CONT_SEMA)))                          
  
WHILE ( @VNNU_CONT_SEMA <= (@VNNU_SEMA_FINA - @VNNU_SEMA_INIC + 1)   AND @VNNU_CONT_SEMA <= 12)           
BEGIN  
  
 --"' + '-' + '" SUBSTRING(DATENAME(MONTH,FE_INIC_SEMA),1,3)    
  
EXECUTE('              
  UPDATE #TWSEMA_FLUJ_Q01          
  SET   ' + @VSDE_CAMP + '= REPLICATE( "0", 2-LEN(RTRIM(LTRIM(STR(T1.NU_SEMA))))) + LTRIM(RTRIM(STR(T1.NU_SEMA))) + REPLICATE( "0", 2-LEN(RTRIM(LTRIM(DAY(T1.FE_INIC_SEMA))))) + LTRIM(RTRIM(DAY(T1.FE_INIC_SEMA))) ' + @VSSIGN + ' SUBSTRING(DATENAME(MONTH,T1.FE_INIC_SEMA),1,3) ' +      " + ' ' + " + ' REPLICATE( "0", 2-LEN(RTRIM(LTRIM(DAY(T1.FE_FINA_SEMA))))) + LTRIM(RTRIM(DAY(T1.FE_FINA_SEMA))) ' + @VSSIGN + ' SUBSTRING(DATENAME(MONTH,T1.FE_FINA_SEMA),1,3)  
   FROM TTSEMA_FLUJ T1  
   WHERE T1.CO_EMPR = "' + @ISCO_EMPR + '"  
   AND T1.NU_ANIO = ' + @VNNU_ANIO + '  
   AND T1.NU_SEMA = ' + @VNNU_SEMA_ACTU +'  
')              
      
SELECT @VNNU_CONT_SEMA = @VNNU_CONT_SEMA + 1, @VNNU_SEMA_ACTU = @VNNU_SEMA_ACTU + 1              
 /* IF @VNNU_MESE = 13              
  BEGIN              
   SELECT @VNNU_MESE = 1              
   SELECT @VNNU_ANIO = @VNNU_ANIO + 1              
  END*/              
  SELECT @VSDE_CAMP = 'FE_SE' + REPLICATE('0', 2-LEN(RTRIM(LTRIM(STR(@VNNU_CONT_SEMA))))) + RTRIM(LTRIM(STR(@VNNU_CONT_SEMA)))              
                      
END          
  
SELECT FE_SE01, FE_SE02, FE_SE03, FE_SE04, FE_SE05, FE_SE06, FE_SE07,   
FE_SE08, FE_SE09, FE_SE10, FE_SE11, FE_SE12
FROM #TWSEMA_FLUJ_Q01  
      
RETURN        
              
/********************************* FIN ***********************************/


GO
